package cn.eeepay.framework.dao;

import cn.eeepay.framework.db.pagination.Page;
import cn.eeepay.framework.model.AgentInfo;
import cn.eeepay.framework.model.BusinessProductDefine;
import cn.eeepay.framework.model.NoticeInfo;
import cn.eeepay.framework.model.NoticeMerScope;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

public interface NoticeInfoDao {

    @Delete({ "delete from notice_info_bak where nt_id = #{id,jdbcType=BIGINT}" })
    int deleteByPrimaryKey(Long id);

	@Options(useGeneratedKeys=true, keyProperty="n.ntId", keyColumn="nt_id")
	@Insert({
			"insert into notice_info (" +
					"nt_id,title,content,attachment,create_time,issued_time,login_user,sys_type,receive_type" +
					",agent_role,agent_no,bp_id,status,issued_org,link,message_img,oem_type,oem_list" +
					",msg_type,valid_begin_time,valid_end_time" +
					",show_status,title_img,notice_type,mer_scope,is_with_param" +
					") values (" +
					"#{n.ntId},#{n.title},#{n.content},#{n.attachment},#{n.createTime},#{n.issuedTime},#{n.loginUser}" +
					",#{n.sysType},#{n.receiveType},#{n.agentRole},#{n.agentNo},#{n.bpId},#{n.status}" +
					",#{n.issuedOrg},#{n.link},#{n.messageImg},#{n.oemType},#{n.oemList},#{n.msgType}" +
					",#{n.validBeginTime},#{n.validEndTime}" +
					",#{n.showStatus},#{n.titleImg},#{n.noticeType},#{n.merScope},#{n.isWithParam}" +
					");"
	})
    int insert(@Param("n")NoticeInfo notice);

	@Select({
			"select * from notice_info where nt_id = #{id} "
	})
    @ResultType(NoticeInfo.class)
    NoticeInfo selectById(@Param("id")String id);
    
    @Update({
			"update notice_info set " +
					"  title = #{record.title},content = #{record.content},attachment = #{record.attachment},sys_type = #{record.sysType},bp_id = #{record.bpId}," +
					"  status = #{record.status},agent_no = #{record.agentNo},receive_type = #{record.receiveType},link=#{record.link},message_img=#{record.messageImg}," +
					"  issued_org=#{record.issuedOrg},issued_org=#{record.issuedOrg},oem_type=#{record.oemType},oem_list=#{record.oemList},valid_begin_time=#{record.validBeginTime}," +
					"  valid_end_time=#{record.validEndTime},show_status=#{record.showStatus},title_img=#{record.titleImg},notice_type=#{record.noticeType},mer_scope=#{record.merScope}," +
					"  is_with_param=#{record.isWithParam} " +
					" where nt_id = #{record.ntId}"
    })
    int update(@Param("record")NoticeInfo record);
    
    @Update({
    	"update notice_info set status='2',issued_org='0',issued_time=now(),login_user=#{map.loginUser} "
    	+ "where nt_id=#{map.id}"
    })
    int deliverNotice(@Param("map")Map<String, Object> map);
    
    @Update("update notice_info set status='1' where nt_id=#{id}")
	int updateRecoverNotice(Integer id);

	@Update("update notice_info set status='3' where nt_id=#{id}")
	int deleteRecoverNotice(Integer id);

	@Update("update notice_info set strong=0 where sys_type=#{sysType}")
	int clearStrongNotice(String sysType);

	@Update("update notice_info set strong=#{strong},issued_time=now() where nt_id=#{id}")
	int strongNotice(@Param("id") Integer id, @Param("strong") Integer strong);

	@Update("update notice_info set strong=#{strong} where nt_id=#{id}")
	int strongNoticeTime(@Param("id") Integer id, @Param("strong") Integer strong);

    @SelectProvider(type=SqlProvider.class,method="selectByParam")
    @ResultType(NoticeInfo.class)
	List<NoticeInfo> selectByParam(@Param("queryaram")NoticeInfo info,Page<NoticeInfo> page);

	@SelectProvider(type=SqlProvider.class,method="listProductByTeams")
	@ResultType(BusinessProductDefine.class)
	List<BusinessProductDefine> listProductByTeams(@Param("ids")String[] ids);

	@Select("select * from agent_info where status='1' and agent_level='1' and (agent_no like #{query_key} or agent_name like #{query_key})")
	@ResultType(AgentInfo.class)
	List<AgentInfo> listTopAgent(@Param("query_key")String query_key);

	@SelectProvider(type=SqlProvider.class,method="pageNoticeMerScope")
	@ResultType(NoticeMerScope.class)
	List<NoticeMerScope> pageNoticeMerScope(@Param("q") NoticeMerScope info, Page<NoticeMerScope> page);

	@Insert("insert into notice_mer_scope (nt_id,mer_no,operator,create_time) values (#{n.ntId},#{n.merchantNo},#{n.operator},NOW());")
	int addNoticeMerScopeRecord(@Param("n")NoticeMerScope scope);

	@Insert("replace into notice_mer_scope (nt_id,mer_no,operator,create_time) values (#{n.ntId},#{n.merchantNo},#{n.operator},NOW());")
	int replaceNoticeMerScopeRecord(@Param("n")NoticeMerScope scope);

	@Insert("delete from notice_mer_scope where nt_id=#{n.ntId}")
	int deleteNoticeMerScopeByNoticeId(@Param("n")NoticeMerScope scope);

	@Delete("delete from notice_mer_scope where id = #{n.id} and nt_id=#{n.ntId} and mer_no=#{n.merchantNo}")
	int delNoticeMerScopeRecord(@Param("n")NoticeMerScope scope);

	@Select("select * from merchant_info where merchant_no = #{query_key} limit 1")
	Map<String,Object> queryMerInfo(@Param("query_key") String queryKey);

	@Insert("<script>" +
			" replace into notice_mer_scope (nt_id,mer_no,operator,create_time) values " +
			" <foreach collection=\"scopes\" item=\"it\" index=\"index\" separator=\",\">" +
			" (#{it.ntId},#{it.merchantNo},#{it.operator},NOW())" +
			" </foreach>" +
			" </script>")
	int batchReplaceNoticeMerScopeRecord(@Param("scopes") List<NoticeMerScope> scopes);

	@Insert("<script>" +
			" INSERT into notice_mer_scope (nt_id,mer_no,operator,create_time) values " +
			" <foreach collection=\"scopes\" item=\"it\" index=\"index\" separator=\",\">" +
			" (#{it.ntId},#{it.merchantNo},#{it.operator},NOW())" +
			" </foreach>" +
			" </script>")
	int batchInsertNoticeMerScopeRecord(@Param("scopes") List<NoticeMerScope> scopes);
    
	class SqlProvider {
		public String selectByParam(final Map<String, Object> param) {
			final NoticeInfo info = (NoticeInfo) param.get("queryaram");
			return new SQL() {
				{
					SELECT("nt_id, title, content, attachment, create_time, issued_time, sys_type, bp_id, status, issued_org, receive_type,"
							+ "link,message_img,strong,msg_type,valid_begin_time,valid_end_time,show_status,notice_type");
					FROM("notice_info");
					if (info.getTitle() != null && StringUtils.isNotBlank(info.getTitle())) {
						info.setTitle(info.getTitle()+"%");
						WHERE("title like #{queryaram.title}");
					}
					if(info.getSysType() != null){
						String sysType = info.getSysType();
						if ( StringUtils.isNotBlank(sysType) && !"0".equals(sysType)) {
							WHERE("sys_type=#{queryaram.sysType}");
						}
					}
					if(info.getMsgType() != null){
						WHERE("msg_type=#{queryaram.msgType}");
					}
					if(StringUtils.isNotBlank(info.getNoticeType())){
						WHERE("notice_type=#{queryaram.noticeType}");
					}
					if(info.getStatus() != null){
						String status = info.getStatus();
						if (StringUtils.isNotBlank(status) && !"0".equals(status)) {
							WHERE("status=#{queryaram.status}");
						}
					}
					if(info.getCreateTimeBegin() != null){
						WHERE("create_time >= #{queryaram.createTimeBegin}");
					}
					if(info.getCreateTimeEnd() != null){
						WHERE("create_time < #{queryaram.createTimeEnd}");
					}
					if(info.getIssuedTimeBegin() != null){
						WHERE("issued_time >= #{queryaram.issuedTimeBegin}");
					}
					if(info.getIssuedTimeEnd() != null){
						WHERE("issued_time < #{queryaram.issuedTimeEnd}");
					}
					if(StringUtils.isNotBlank(info.getShowStatus())){
						WHERE("show_status = #{queryaram.showStatus}");
					}
					WHERE("status != 3");			//状态为3的 表示是已删除的消息
					ORDER_BY("strong desc");
					ORDER_BY("IF (isnull(issued_time), 0, 1) ASC");
					ORDER_BY("issued_time desc");
					ORDER_BY("create_time desc");
				}
			}.toString();
		}

		public String propertyMapping(String name, int type) {
			final String[] propertys = {"ntId", "title", "content", "attachment", "createTime", "issuedTime",
					"sysType", "bpId","status", "receiveType","agentNo","issuedOrg","loginUser"};
			final String[] columns = { "id", "nt_id", "title", "content", "attachment", "create_time", "issued_time",
					"sys_type", "bp_id","status","receive_type","agent_no","issued_org","login_user"};
			if (StringUtils.isNotBlank(name)) {
				if (type == 0) {// 属性查出字段名
					for (int i = 0; i < propertys.length; i++) {
						if (name.equalsIgnoreCase(propertys[i])) {
							return columns[i];
						}
					}
				} else if (type == 1) {// 字段名查出属性
					for (int i = 0; i < propertys.length; i++) {
						if (name.equalsIgnoreCase(columns[i])) {
							return propertys[i];
						}
					}
				}
			}
			return null;
		}

		public String listProductByTeams(final Map<String, Object> param){
			String[] ids = (String[]) param.get("ids");
			StringBuffer sql = new StringBuffer("select bpd.* from business_product_define bpd")
//					.append(" inner join agent_business_product abp on bpd.bp_id = abp.bp_id")
//					.append(" inner join agent_info ai on abp.agent_no = ai.agent_no")
					.append(" where bpd.effective_status = 1 and bpd.team_id in (")
					.append(String.join(",", ids))
					.append(") order by bpd.bp_id");
			return sql.toString();
		}

		public String pageNoticeMerScope(final Map<String, Object> param) {
			final NoticeMerScope scope = (NoticeMerScope) param.get("q");
			return new SQL() {
				{
					SELECT("nms.id,nms.nt_id,mi.team_id,mi.merchant_no,mi.merchant_name,mi.mobilephone,ti.team_name,nms.operator,nms.create_time");
					FROM("notice_mer_scope nms");
					INNER_JOIN("merchant_info mi on nms.mer_no = mi.merchant_no");
					INNER_JOIN("team_info ti on mi.team_id = ti.team_id");
					if (scope.getId()!=null) {
						WHERE("nms.id = #{q.id}");
					}
					if (scope.getNtId()!=null) {
						WHERE("nms.nt_id = #{q.ntId}");
					}
					if (StringUtils.isNotBlank(scope.getMerchantNo())) {
						WHERE("nms.mer_no = #{q.merchantNo}");
					}
					if (StringUtils.isNotBlank(scope.getMobilephone())) {
						WHERE("mi.mobilephone like #{q.mobilephone}");
					}
					if (StringUtils.isNotBlank(scope.getMerchantName())) {
						WHERE("mi.merchant_name like #{q.merchantName}");
					}
					ORDER_BY("nms.id desc");
				}
			}.toString();
		}
	}
}